package belf.migrate.plugin.shentong;

import belf.migrate.api.common.DatabaseIdentifier;
import belf.migrate.api.constants.SQLConstants;
import belf.migrate.api.exception.CatalogException;
import belf.migrate.api.exception.DataTypeMappingNotExistException;
import belf.migrate.api.exception.DatabaseNotExistException;
import belf.migrate.api.exception.TableNotExistException;
import belf.migrate.api.model.TableInfoModel;
import belf.migrate.api.table.schema.*;
import belf.migrate.api.taskconf.TaskConf;
import belf.migrate.api.util.JdbcUrlUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class ShentongCatalog extends AbstractSinkCatalog {
    private static final String QUOTE = "";

    public ShentongCatalog(String catalogName, String username, String pwd, JdbcUrlUtil.UrlInfo urlInfo, String defaultSchema, boolean caseSensitive) {
        super(DatabaseIdentifier.SHENTONG, username, pwd, urlInfo, defaultSchema, caseSensitive);
    }

    @Override
    public CatalogTable getTable(TablePath tablePath, List<String> fieldNames) throws TableNotExistException {
        return super.getTable(tablePath, fieldNames);
    }

    @Override
    public List<UserDefType> listTypes(String schema, List<String> typeNames) throws CatalogException {
        return super.listTypes(schema, typeNames);
    }

    @Override
    public List<UserDefSeq> listSequences(String owner, List<String> sequenceNames) throws CatalogException {
        return super.listSequences(owner, sequenceNames);
    }

    @Override
    public List<UserDefFunction> listFunctions(String schema, List<String> functionNames) throws CatalogException {
        return super.listFunctions(schema, functionNames);
    }

    @Override
    public List<UserDefSynonym> listSynonyms(String schema, List<String> synonymNames) throws CatalogException {
        return super.listSynonyms(schema, synonymNames);
    }

    @Override
    public List<String> listTypeColumns(String owner, String typeName) throws CatalogException {
        return super.listTypeColumns(owner, typeName);
    }

    @Override
    public String getListDatabaseSQL() {
        return """
                SELECT NSPNAME AS DATABASE_NAME FROM INFO_SCHEM.V_SYS_NAMESPACE WHERE ( NSPNAME IS NOT NULL)
                """;
    }

    @Override
    public String getListTableSQL(String database, String schema) {
        return String.format("""
                SELECT TABLENAME AS TABLE_NAME FROM v_sys_table WHERE SCHEMANAME='%s'
                """, schema);
    }

    @Override
    public String getListProcedureSQL(String database) {
        return String.format("""
                SELECT F.proname AS PROCEDURE_NAME FROM INFO_SCHEM.V_SYS_PROC AS F, INFO_SCHEM.V_SYS_LANGUAGE AS L, INFO_SCHEM.ALL_OBJECTS AS O, INFO_SCHEM.V_SYS_NAMESPACE AS n
                WHERE pronamespace = n.oid AND F.prolang = L.OID AND L.lanname = 'ploscar' AND F.proisfunc = 0 AND proname NOT LIKE '%%Trig_Proc_%%' AND F.OID = O.OBJECT_ID AND ( n.NSPNAME IS NOT NULL) AND n.NSPNAME = '%s'
                ORDER BY proname
                """, database);
    }

    @Override
    public String getListFunctionSQL(String database) {
        return String.format("""
                SELECT F.proname AS FUNCTION_NAME FROM INFO_SCHEM.V_SYS_PROC AS F, INFO_SCHEM.V_SYS_LANGUAGE AS L, INFO_SCHEM.ALL_OBJECTS AS O, INFO_SCHEM.V_SYS_NAMESPACE AS N
                WHERE pronamespace = N.OID AND F.prolang = L.OID AND L.lanname = 'ploscar' AND F.proisfunc = 1 AND F.OID = O.OBJECT_ID AND (N.NSPNAME IS NOT NULL) AND N.NSPNAME = '%s'
                ORDER BY proname
                """, username.toUpperCase());
    }

    @Override
    public String getListTriggerSQL(String database) {
        return String.format("""
                SELECT t.tgname AS TRIGGER_NAME	FROM INFO_SCHEM.v_sys_trigger AS t LEFT JOIN INFO_SCHEM.V_SYS_CLASS C ON t.tgrelid = C.OID, INFO_SCHEM.v_sys_proc AS p, INFO_SCHEM.V_SYS_NAMESPACE AS n
                                  WHERE t.tgfoid = p.oid AND (c.relnamespace = n.oid OR c.relnamespace IS NULL ) AND p.PRONAMESPACE = n.oid AND tgisconstraint = FALSE AND t.tgname NOT LIKE '__MVIEW_LOG%%' AND ( n.NSPNAME IS NOT NULL) AND n.NSPNAME = '%s'
                """, database);
    }

    @Override
    public String getListSequenceSQL(String database) {
        return String.format("""
                SELECT SEQUENCENAME AS SEQUENCE_NAME FROM INFO_SCHEM.v_sys_sequence where SCHEMANAME='%s' ORDER BY SEQUENCENAME
                """, username.toUpperCase());
    }

    @Override
    public String getListSynonymSQL(String database) {
        return String.format("""
                SELECT syn.SYNONYM_NAME AS SYNONYM_NAME FROM (SELECT TABLE_NSPNAME, DB_LINK, SYNONYM_NSPNAME, TABLE_NAME AS OBJECT_NAME, SYNONYM_NAME FROM INFO_SCHEM.v_sys_synonyms) syn
                LEFT JOIN(SELECT o.oid, n.nspname, o.relname, o.relkind FROM INFO_SCHEM.v_sys_namespace n, INFO_SCHEM.v_sys_class o WHERE o.relnamespace = n.oid) tmp ON syn.TABLE_NSPNAME = tmp.nspname AND syn.OBJECT_NAME = tmp.relname
                WHERE syn.SYNONYM_NSPNAME = (SELECT OID FROM INFO_SCHEM.V_SYS_NAMESPACE WHERE (NSPNAME IS NOT NULL) AND NSPNAME = '%s')
                ORDER BY syn.SYNONYM_NAME
                """, username.toUpperCase());
    }

    @Override
    public String getSelectColumnsSql(TablePath tablePath) {
        return "SELECT * FROM " + tablePath.getSchemaAndTableName();
    }

    @Override
    public String getCountDataSql(TablePath tablePath) {
        return String.format("""
                SELECT count(*) FROM %s.%s LIMIT 1;
                """, tablePath.getSchemaName(), tablePath.getTableName());
    }

    @Override
    public List<TableInfoModel> listTablesInfo(String databaseName, String schemaName) throws CatalogException, DatabaseNotExistException {
        return null;
    }

    @Override
    public String getFullTableName(TablePath tablePath) {
        return tablePath.getTableName();
    }

    @Override
    public String getQuote() {
        return QUOTE;
    }

    @Override
    public List<String> specialColumns() {
        return super.specialColumns();
    }

    @Override
    public List<String> bigDataColumns() {
        return List.of("LONGVARCHAR", "IMAGE", "LONGVARBINARY", "BLOB", "BFILE");
    }

    protected String getColumnsSql(TablePath tablePath) {
        return "";
    }

    @Override
    public String getListViewsSQL(String databaseName) {
        return String.format("""
                SELECT C.RELNAME AS VIEW_NAME FROM INFO_SCHEM.V_SYS_CLASS C, INFO_SCHEM.V_SYS_NAMESPACE AS N
                WHERE C.RELKIND = 'v' AND (N.NSPNAME IS NOT NULL) AND N.NSPNAME = '%s' AND C.RELNAMESPACE = N.OID AND C.OID NOT IN(SELECT C.OID FROM INFO_SCHEM.V_SYS_EXTERNAL_TABLE)
                """, databaseName);
    }

    @Override
    protected String getListViewsSQLInfo(String defaultDatabase, String schemaName) {
        return String.format("""
                SELECT C.RELNAME AS VIEW_NAME FROM INFO_SCHEM.V_SYS_CLASS C, INFO_SCHEM.V_SYS_NAMESPACE AS N
                WHERE C.RELKIND = 'v' AND (N.NSPNAME IS NOT NULL) AND N.NSPNAME = '%s' AND C.RELNAMESPACE = N.OID AND C.OID NOT IN(SELECT C.OID FROM INFO_SCHEM.V_SYS_EXTERNAL_TABLE)
                """, schemaName);
    }

    @Override
    public String getListTypeSQL(String defaultDatabase) {
        return String.format("""
                SELECT O.TYPNAME AS TYPE_NAME FROM INFO_SCHEM.V_SYS_TYPE O, INFO_SCHEM.ALL_OBJECTS OBJ, INFO_SCHEM.V_SYS_NAMESPACE AS N
                WHERE (N.NSPNAME IS NOT NULL) AND N.NSPNAME = '%s' AND O.TYPNAMESPACE = N.OID AND O.OID = OBJ.OBJECT_ID
                ORDER BY O.TYPNAME
                """, defaultDatabase);
    }

    @Override
    public String getDropViewSql(TablePath viewPath) {
        return String.format("DROP VIEW IF EXISTS %s CASCADE",
                viewPath.getSchemaAndTableName());
    }

    @Override
    protected String getListIndexSQL(String defaultDatabase) {
        return "select INDEX_NAME,TABLE_NAME,INDEX_TYPE from USER_INDEXES";
    }

    @Override
    public List<UniqueKey> getUniqueKeys(TablePath tablePath) throws CatalogException {
        return null;
    }

    @Override
    public List<CheckConstraint> getCheckConstraints(TablePath tablePath) throws CatalogException {
        return null;
    }

    @Override
    public Object getColumnValueByType(ColumnMetaData columnMetaData, ResultSet resultSet, int index) throws SQLException {
        return null;
    }

    @Override
    public boolean dataEqualByType(String type, Object sourceData, Object sinkData) {
        return false;
    }

    @Override
    public void setCurrentSchema(Connection connection, String schema) throws SQLException {

    }

    @Override
    public String getCreateTableSql(TablePath tablePath, CatalogTable table, TaskConf taskConf) throws DataTypeMappingNotExistException {
        return ShentongCreateTableSqlBuilder.builder(tablePath,
                        table.getTableSchema(),
                        table.getCatalogName() + "-" + DatabaseIdentifier.SHENTONG,
                        taskConf)
                .fieldCase("UPPERCASE")
                .createTable();
    }

    @Override
    public List<String> getCreateIndexSqls(TablePath tablePath, CatalogTable table, TaskConf taskConf) {
        return ShentongCreateTableSqlBuilder.builder(tablePath,
                        table.getTableSchema(),
                        table.getCatalogName() + "-" + DatabaseIdentifier.SHENTONG)
                .fieldCase("UPPERCASE")
                .createIndexes();
    }

    @Override
    public List<String> getCreateForeignSqls(TablePath tablePath, CatalogTable table, TaskConf taskConf) {
        return ShentongCreateTableSqlBuilder.builder(tablePath,
                        table.getTableSchema(),
                        table.getCatalogName() + "-" + DatabaseIdentifier.SHENTONG)
                .fieldCase("UPPERCASE").createForeigns();
    }

    @Override
    public String[] getScriptDelimiters() {
        return SQLConstants.DEFAULT_SCRIPT_DELIMITER;
    }

    @Override
    public String[][] getBlockBoundStrings() {
        return new String[][]{
                {SQLConstants.BLOCK_BEGIN, SQLConstants.BLOCK_END},
                {"IF", SQLConstants.BLOCK_END},
                {"LOOP", SQLConstants.BLOCK_END + " LOOP"},
                {SQLConstants.KEYWORD_CASE, SQLConstants.BLOCK_END + " " + SQLConstants.KEYWORD_CASE},
        };
    }

}
